package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import control.ConnectionManage;


import db.parking.beans.SavedLot;


public class AdminSavedLot {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	/*public static void displayAllRows() throws SQLException {

		String sql = "SELECT *  FROM Comlaints";
		try (
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				){

			System.out.println("Complaints Table:");
			while (rs.next()) {
				StringBuffer bf = new StringBuffer();
				bf.append(rs.getInt("id") + ": ");
				bf.append(rs.getString("username") +", ");
				bf.append(rs.getString("age"));
				System.out.println(bf.toString());
			}
		}
	}
*/
	public static SavedLot getRow(int lotId,String uid) throws SQLException {

		String sql = "SELECT * FROM SavedLot WHERE lotId  = ? AND uid = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, lotId);
			stmt.setString(1, uid);
			rs = stmt.executeQuery();

			if (rs.next()) {
				SavedLot bean = new SavedLot();
				bean.setLotID(lotId);
				bean.setUid(uid);
				bean.setCarNo(rs.getString("carNo"));
				bean.setFrom(rs.getString("from"));
				bean.setTo(rs.getString("to"));
				bean.setDate(rs.getString("date"));
				bean.setStatus(rs.getInt("status"));
				
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	public static boolean insert(SavedLot bean) throws Exception {
    //complaintID is autoIncremented on Insert
		String sql = "INSERT into SavedLot (lotID , uid , carNo , from , to , date , status) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?)";
		ResultSet keys = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			stmt.setInt(1, bean.getLotID());
			stmt.setString(2, bean.getUid());
			stmt.setString(3, bean.getCarNo());
			stmt.setString(4, bean.getFrom());
			stmt.setString(5, bean.getTo());
			stmt.setString(6, bean.getDate());
			stmt.setInt(7, bean.getStatus());
			stmt.executeUpdate();
			
			
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
			if(keys != null)
				keys.close();
		}
		return true;
		
	}
	//update complaint status
	public static boolean update(SavedLot bean) throws Exception {

		String sql =
				"UPDATE Complaints SET " +
				"status = ? " +
				"WHERE lotID = ? AND uid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getLotID());
			stmt.setString(3,bean.getUid());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	//delete saved parking space by ot number and uid
	public static boolean delete(int lotId,String uid) throws Exception {

		String sql = "DELETE FROM rates WHERE lotID = ? AND uid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,lotId);
			stmt.setString(2,uid);
			int affected = stmt.executeUpdate();
			if(affected == 1){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
}
